package excel;

import com.microsoft.schemas.office.excel.CTClientData;
import com.microsoft.schemas.vml.CTShape;
import com.microsoft.schemas.vml.CTShapetype;
import com.microsoft.schemas.vml.CTTextbox;
import com.microsoft.schemas.vml.STTrueFalse;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.ooxml.POIXMLDocumentPart;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.*;
import org.apache.xmlbeans.XmlObject;
import org.openxmlformats.schemas.drawingml.x2006.main.STLineEndType;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTDrawing;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTLegacyDrawing;
import org.openxmlformats.schemas.spreadsheetml.x2006.main.CTWorksheet;

import javax.imageio.ImageIO;
import javax.xml.namespace.QName;
import java.awt.*;
import java.awt.image.BufferedImage;
import java.io.ByteArrayOutputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.util.List;

public class AddExcelXSSFControls {

    public static void main(String[] args) throws Exception {

        XSSFWorkbook workbook = new XSSFWorkbook();

        //following is necessary to be textboxHTML of the CTShape compatible with Excel 2007.
        //<fileVersion appName="xl" lastEdited="4" lowestEdited="0" rupBuild="4507"/>
        // workbook.getCTWorkbook().addNewFileVersion().setAppName("xl");
        // workbook.getCTWorkbook().getFileVersion().setLastEdited("4");
        // workbook.getCTWorkbook().getFileVersion().setLowestEdited("0");
        // workbook.getCTWorkbook().getFileVersion().setRupBuild("4507");

        XSSFSheet sheet = workbook.createSheet();
        XSSFCell cell = sheet.createRow(5).createCell(5);
        /*
          XSSFDrawing drawing = sheet.createDrawingPatriarch();
          XSSFClientAnchor anchor = workbook.getCreationHelper().createClientAnchor();
          anchor.setCol1(cell.getColumnIndex());
          anchor.setCol2(cell.getColumnIndex()+1);
          anchor.setRow1(cell.getRow().getRowNum());
          anchor.setRow2(cell.getRow().getRowNum()+3);
          XSSFComment comment = drawing.createCellComment(anchor);

          XSSFRichTextString str = workbook.getCreationHelper().createRichTextString("Hello, World!");
          comment.setString(str);
          comment.setAuthor("Apache POI");
          cell.setCellComment(comment);
        */

        /*// 获取画布
        XSSFVMLDrawing vmlDrawing = getVMLDrawing(sheet);
        addCheckboxShapeType(vmlDrawing);

        addCheckbox(vmlDrawing, 1, 0, 1, 0, 3, 0, 2, 0, "Checkbox 1", true);
        addCheckbox(vmlDrawing, 1, 0, 2, 0, 3, 0, 3, 0, "Checkbox 2", false);
        */

        // 顶级画布，只能有一个 无则创建，有则返回
        XSSFDrawing drawing = sheet.createDrawingPatriarch();
        CTWorksheet ctWorksheet = sheet.getCTWorksheet();

        CTDrawing drawing1 = ctWorksheet.getDrawing(); // XSSFDrawing xl\drawings\drawing1.xml
        String id1 = drawing1.getId();
        POIXMLDocumentPart part1 = sheet.getRelationById(id1);

        CTLegacyDrawing legacyDrawing = ctWorksheet.getLegacyDrawing(); // XSSFVMLDrawing xl\drawings\vmlDrawing1.vml

        if (legacyDrawing != null) {
            String legacyDrawingId = legacyDrawing.getId();
            POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);

            if (part instanceof XSSFDrawing) {
                System.out.println("instanceof XSSFDrawing");
            }
            if (part instanceof XSSFVMLDrawing) {
                // relations一致
                // Name: /xl/drawings/vmlDrawing1.vml - Content Type: application/vnd.openxmlformats-officedocument.vmlDrawing
                XSSFVMLDrawing vml = (XSSFVMLDrawing) part;
                System.out.println();
            }
        } else {
            // 不是这样创建
            // CTLegacyDrawing legacyDrawing = ctWorksheet.addNewLegacyDrawing();

            // sheet.getVMLDrawing(true);
            Method _method = XSSFSheet.class.getDeclaredMethod("getVMLDrawing", boolean.class);
            _method.setAccessible(true);
            XSSFVMLDrawing xssfvmlDrawing = (XSSFVMLDrawing)_method.invoke(sheet, true);
            // POIXMLDocumentPart.RelationPart rp = (POIXMLDocumentPart.RelationPart) _method.invoke(new POIXMLDocumentPart(), XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber, false);
            // POIXMLDocumentPart relationship = sheet.createRelationship(XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber);
            // drawing = (XSSFVMLDrawing)relationship;

            legacyDrawing = ctWorksheet.getLegacyDrawing();
            String legacyDrawingId = legacyDrawing.getId();
            POIXMLDocumentPart part = sheet.getRelationById(legacyDrawingId);

            if (part instanceof XSSFVMLDrawing) {
                // relations一致
                // Name: /xl/drawings/vmlDrawing1.vml - Content Type: application/vnd.openxmlformats-officedocument.vmlDrawing
                XSSFVMLDrawing vml = (XSSFVMLDrawing) part;
                System.out.println();

                addCheckbox(vml, 1, 0, 1, 0, 3, 0, 2, 0, "Checkbox 1", true);
                addCheckbox(vml, 1, 0, 2, 0, 3, 0, 3, 0, "Checkbox 2", false);
                addCheckbox(vml, 1, 0, 3, 0, 5, 0, 5, 0, "Checkbox 3", true);
            }

        }

        // POI 导出 Excel 并且根据内容设置列宽自适应 https://my.oschina.net/u/2331760/blog/3050616?widget=related-article-list&p=2&type=ajax
        XSSFRow row = sheet.createRow(0);
        //heightInPoints 设置的值永远是 height 属性值的 20 倍
        row.setHeightInPoints(20);

        XSSFRow row1 = sheet.createRow(1);
        row1.setHeight((short) (25 * 20));

        XSSFRow row2 = sheet.createRow(2);
        row2.setHeight((short) (25 * 20));


        // 插入箭头
        createArrow(sheet, 0, 0, 2, 2);

        // 绘制水印
        BufferedImage watermarkImage = createWatermarkImage("我是水印", 100, 100);
        putWaterRemarkToExcel(workbook, sheet, watermarkImage);

        // excel创建的复选框和代码创建的格式不一样 表格控件不会生成文件： xl\ctrlProps\ctrlProp1.xml
        FileOutputStream out = new FileOutputStream("Excel.xlsx");
        workbook.write(out);
        out.close();
        workbook.close();

    }


    /**
     * 绘制箭头
     * 关于POI工具类绘制水印，箭头等功能 https://www.cnblogs.com/gaofugui/p/15796788.html
     *
     * @param sheet
     * @param col1  第1个单元格的行号
     * @param col1  第1个单元格的列号
     * @param row1  第2个单元格的行号
     * @param row2  第2个单元格的列号
     */
    public static void createArrow(XSSFSheet sheet, int col1, int row1, int col2, int row2) {
        // 计算偏移量
        int dx1 = (sheet.getColumnWidth(col1) / 2 + 500) * 255;
        int dy1 = (sheet.getRow(row1).getHeight() + 800) * 255;
        int dx2 = (sheet.getColumnWidth(col2) / 2 + 500) * 255;
        int dy2 = (sheet.getRow(row2).getHeight() - 200) * 255;

        XSSFDrawing xssfdrawing = sheet.createDrawingPatriarch(); //顶级画布，只能有一个
        /** 从左上角到右下角 */
        if (col1 <= col2 && row1 <= row2) {
            XSSFClientAnchor xssfanchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row1, col2, row2);
            XSSFSimpleShape xssfshape = xssfdrawing.createSimpleShape(xssfanchor);
            xssfshape.setShapeType(ShapeTypes.LINE);
            xssfshape.setLineWidth(1.5);
            xssfshape.setLineStyle(0);
            xssfshape.setLineStyleColor(0, 0, 0);
            xssfshape.getCTShape().getSpPr().getLn().addNewTailEnd().setType(STLineEndType.TRIANGLE);
        }
        /** 从左下角到右上角 */
        if (col1 <= col2 && row1 > row2) {
            // 方法只支持col1<col2且row1<row2的情况，所以此处需要反向传值，后面再反射显示
            XSSFClientAnchor xssfanchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col1, row2, col2, row1);
            XSSFSimpleShape xssfshape = xssfdrawing.createSimpleShape(xssfanchor);
            xssfshape.setShapeType(ShapeTypes.LINE);
            xssfshape.setLineWidth(1.5);
            xssfshape.setLineStyle(0);
            xssfshape.setLineStyleColor(0, 0, 0);
            xssfshape.getCTShape().getSpPr().getXfrm().setFlipV(true);  // 将图形反射显示
            xssfshape.getCTShape().getSpPr().getLn().addNewTailEnd().setType(STLineEndType.TRIANGLE);
        }
        /** 从右下角到左上角 */
        if (col1 > col2 && row1 > row2) {
            XSSFClientAnchor xssfanchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col2, row2, col1, row1);
            XSSFSimpleShape xssfshape = xssfdrawing.createSimpleShape(xssfanchor);
            xssfshape.setShapeType(ShapeTypes.LINE);
            xssfshape.setLineWidth(1.5);
            xssfshape.setLineStyle(0);
            xssfshape.setLineStyleColor(0, 0, 0);
            // 由于线是从左上角到右下角绘制，所以此处将箭头标在起点处
            xssfshape.getCTShape().getSpPr().getLn().addNewHeadEnd().setType(STLineEndType.TRIANGLE);
        }
        /** 从右上角到左下角 */
        if (col1 > col2 && row1 <= row2) {
            // 方法只支持col1<col2且row1<row2的情况，所以此处需要反向传值，后面再反射显示
            XSSFClientAnchor xssfanchor = new XSSFClientAnchor(dx1, dy1, dx2, dy2, col2, row1, col1, row2);
            XSSFSimpleShape xssfshape = xssfdrawing.createSimpleShape(xssfanchor);
            xssfshape.setShapeType(ShapeTypes.LINE);
            xssfshape.setLineWidth(1.5);
            xssfshape.setLineStyle(0);
            xssfshape.setLineStyleColor(0, 0, 0);
            xssfshape.getCTShape().getSpPr().getXfrm().setFlipV(true); // 将图形反射显示
            xssfshape.getCTShape().getSpPr().getLn().addNewHeadEnd().setType(STLineEndType.TRIANGLE);
        }
    }

    /**
     * 为Excel打上水印工具函数
     * 关于POI工具类绘制水印，箭头等功能 https://www.cnblogs.com/gaofugui/p/15796788.html
     * 请自行确保参数值，以保证水印图片之间不会覆盖。
     * 在计算水印的位置的时候，并没有考虑到单元格合并的情况，请注意
     *
     * @param wb              Excel Workbook
     * @param sheet           需要打水印的Excel
     * @param waterRemarkPath 水印地址，classPath，目前只支持png格式的图片，
     *                        因为非png格式的图片打到Excel上后可能会有图片变红的问题，且不容易做出透明效果。
     *                        同时请注意传入的地址格式，应该为类似："\\excelTemplate\\test.png"
     * @throws IOException
     */
    public static void putWaterRemarkToExcel(Workbook wb, Sheet sheet, BufferedImage waterRemarkPath) throws IOException {

        //加载图片
        ByteArrayOutputStream byteArrayOut = new ByteArrayOutputStream();
        if (null == waterRemarkPath) {
            throw new RuntimeException("向Excel上面打印水印，读取水印图片失败(2)。");
        }
        ImageIO.write(waterRemarkPath, "png", byteArrayOut);
        //开始打水印
        Drawing drawing = sheet.createDrawingPatriarch();
        /*
         * 参数定义：
         * 第一个参数是（x轴的开始节点）；
         * 第二个参数是（是y轴的开始节点）；
         * 第三个参数是（是x轴的结束节点）；
         * 第四个参数是（是y轴的结束节点）；
         * 第五个参数是（是从Excel的第几列开始插入图片，从0开始计数）；
         * 第六个参数是（是从excel的第几行开始插入图片，从0开始计数）；
         * 第七个参数是（图片宽度，共多少列）；
         * 第8个参数是（图片高度，共多少行）；
         */
        ClientAnchor anchor = drawing.createAnchor(0, 0, 255, 255, 0, 0, 1, 1);
        Picture pic = drawing.createPicture(anchor, wb.addPicture(byteArrayOut.toByteArray(), Workbook.PICTURE_TYPE_PNG));
        pic.resize();


        // 将表格设为只读，否则水印就没了意义
//        sheet.protectSheet(UUID.randomUUID().toString());
    }

    /**
     * 绘制水印图片
     *
     * @param text
     * @param width
     * @param height
     * @return
     */
    public static BufferedImage createWatermarkImage(String text, Integer width, Integer height) {
        java.awt.Font font = new java.awt.Font("microsoft-yahei", java.awt.Font.PLAIN, 20);
        BufferedImage image = new BufferedImage(width, height, BufferedImage.TYPE_INT_RGB);
        // ----------  增加下面的代码使得背景透明  -----------------
        Graphics2D g = image.createGraphics();
        image = g.getDeviceConfiguration().createCompatibleImage(width, height, Transparency.TRANSLUCENT);
        g.dispose();
        // ----------  背景透明代码结束  -----------------

        g = image.createGraphics();
        g.setColor(new java.awt.Color(Integer.parseInt("C5CBCF", 16)));// 设定画笔颜色
        g.setFont(font);// 设置画笔字体
        g.shear(0.1, -0.26);// 设定倾斜度
        g.setRenderingHint(RenderingHints.KEY_ANTIALIASING, RenderingHints.VALUE_ANTIALIAS_ON); // 设置字体平滑


        // 字体长度
        //int markWidth = font.getSize() * getTextLength (text);
        int markWidth = font.getSize() * text.length();
        // 字体高度
        int markHeight = font.getSize();

        // 循环添加水印
        int x = 0;
        int y = 50;
        while (x < width) {
            while (y < height) {
                g.drawString(text, x, y);
                y += markHeight + 100;
            }
            x += markWidth + 150;
            y = 50;
        }


        g.dispose();// 释放画笔
        return image;

    }

    /**
     * 创建画布 有则返回，无则创建
     * POI (Java) 在 XLSX 中创建复选框
     * https://www.it1352.com/2552333.html
     *
     * @param sheet
     */
    private static XSSFVMLDrawing getVMLDrawing(XSSFSheet sheet) throws Exception {
        XSSFVMLDrawing drawing = null;
        CTWorksheet ctWorksheet = sheet.getCTWorksheet();

        if (ctWorksheet.getLegacyDrawing() != null) {
            String legacyDrawingId = ctWorksheet.getLegacyDrawing().getId();
            drawing = (XSSFVMLDrawing) sheet.getRelationById(legacyDrawingId);
        } else {
            int drawingNumber = sheet.getPackagePart().getPackage()
                    .getPartsByContentType(XSSFRelation.VML_DRAWINGS.getContentType()).size() + 1;

            POIXMLDocumentPart.RelationPart rp =
                    sheet.createRelationship(XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber, false);

            // 3.12 版本方法无权限调用
            // POIXMLDocumentPart protected final POIXMLDocumentPart.RelationPart createRelationship(POIXMLRelation descriptor, POIXMLFactory factory, int idx, boolean noRelation)
            // 反射调用方法
            // Method _method = POIXMLDocumentPart.class.getDeclaredMethod("createRelationship", POIXMLRelation.class, POIXMLFactory.class, int.class, boolean.class);
            // _method.setAccessible(true);
            // POIXMLDocumentPart.RelationPart rp = (POIXMLDocumentPart.RelationPart) _method.invoke(new POIXMLDocumentPart(), XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber, false);
            // POIXMLDocumentPart relationship = sheet.createRelationship(XSSFRelation.VML_DRAWINGS, XSSFFactory.getInstance(), drawingNumber);
            // drawing = (XSSFVMLDrawing)relationship;

            drawing = rp.getDocumentPart();
            String rId = rp.getRelationship().getId();
            ctWorksheet.addNewLegacyDrawing().setId(rId);

        }
        return drawing;
    }


    private static void addCheckboxShapeType(XSSFVMLDrawing drawing) throws Exception {
        /**
         * xl\drawings\vmlDrawing1.vml
         * <v:shapetype id="_x0000_t201"
         * <v:shape id="_x0000_s1025"
         */
        String shapeTypeId = "_x0000_t201";
        CTShapetype shapeType = CTShapetype.Factory.newInstance();
        shapeType.setId(shapeTypeId);
        shapeType.setCoordsize("21600,21600");
        shapeType.setSpt(201);
        shapeType.setPath2("m,l,21600r21600,l21600,xe");

        Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
        _items.setAccessible(true);
        @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
        List<XmlObject> items = (List<XmlObject>) _items.get(drawing);

        Field _qnames = XSSFVMLDrawing.class.getDeclaredField("_qnames");
        _qnames.setAccessible(true);
        @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
        List<QName> qnames = (List<QName>) _qnames.get(drawing);

        items.add(shapeType);
        qnames.add(new QName("urn:schemas-microsoft-com:vml", "shapetype"));
    }


    private static void addCheckbox(XSSFVMLDrawing drawing,
                                    int col1, int dx1, int row1, int dy1, int col2, int dx2, int row2, int dy2,
                                    String label, boolean checked) throws Exception {

        String shapeTypeId = "_x0000_t201";

        Field _shapeId = XSSFVMLDrawing.class.getDeclaredField("_shapeId");
        _shapeId.setAccessible(true);
        int shapeId = (int) _shapeId.get(drawing);
        _shapeId.set(drawing, shapeId + 1);

        CTShape shape = CTShape.Factory.newInstance();
        shape.setId("_x0000_s" + shapeId);
        shape.setType("#" + shapeTypeId);
        shape.setFilled(STTrueFalse.F);
        shape.setStroked(STTrueFalse.F);
        String textboxHTML =
                "<div style='text-align:left'>"
                        + "<font face=\"Tahoma\" size=\"160\" color=\"auto\">" + label + "</font>"
                        + "</div>";
        CTTextbox[] textboxArray = new CTTextbox[1];
        textboxArray[0] = CTTextbox.Factory.parse(textboxHTML);
        textboxArray[0].setStyle("mso-direction-alt:auto");
        // textboxArray[0].setSingleclick(com.microsoft.schemas.office.office.STTrueFalse.F);
        // textboxArray[0].xsetSingleclick(com.microsoft.schemas.vml.STTrueFalse.F);
        // textboxArray[0].xsetSingleclick();
        shape.setTextboxArray(textboxArray);

        CTClientData cldata = shape.addNewClientData();
        cldata.setObjectType(com.microsoft.schemas.office.excel.STObjectType.CHECKBOX);
        cldata.addNewMoveWithCells();
        cldata.addNewSizeWithCells();
        cldata.addNewAnchor().setStringValue(
                "" + col1 + ", " + dx1 + ", " + row1 + ", " + dy1 + ", " + col2 + ", " + dx2 + ", " + row2 + ", " + dy2
        );
        cldata.addAutoFill(com.microsoft.schemas.office.excel.STTrueFalseBlank.FALSE);
        cldata.addAutoLine(com.microsoft.schemas.office.excel.STTrueFalseBlank.FALSE);
        cldata.addTextVAlign("Center");
        cldata.addNoThreeD(com.microsoft.schemas.office.excel.STTrueFalseBlank.TRUE);

        cldata.addChecked((checked) ? java.math.BigInteger.valueOf(1) : java.math.BigInteger.valueOf(0));

        Field _items = XSSFVMLDrawing.class.getDeclaredField("_items");
        _items.setAccessible(true);
        @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
        List<XmlObject> items = (List<XmlObject>) _items.get(drawing);

        Field _qnames = XSSFVMLDrawing.class.getDeclaredField("_qnames");
        _qnames.setAccessible(true);
        @SuppressWarnings("unchecked") //we know the problem and expect runtime error if it possibly occurs
        List<QName> qnames = (List<QName>) _qnames.get(drawing);

        items.add(shape);
        qnames.add(new QName("urn:schemas-microsoft-com:vml", "shape"));

    }

}
